package com.roc.web.action.estrn;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;

public class CommNumberStatSearchCondtion  extends BaseSearchCondition{
	
	private String companyName;	//分公司
	private String commNumber;//总商户数量
	//private String sunCommNumber;//当日商户数量
	private String sunPieceNumber;//当日进件数量
	private String areaCode;//所属区域
	private String minDate;//日期
	private String maxDate;
	private long id;
	
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		// TODO Auto-generated method stub
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
		if (Constance.isNotEmpty(companyName)) {
			filedBeanList.add(new WhereHQLFieldBean("x.org_name", companyName.trim(), "=", false));
		}
		
		
		return filedBeanList;
	}

	@Override
	protected String getRowCountHQL() {
		// TODO Auto-generated method stub
			StringBuffer  sb= new StringBuffer();
			sb.append("SELECT count(1) ");
			sb.append(" FROM (SELECT c.org_name, d.name , COUNT(1) AS zhong " );
			sb.append(" FROM apsmgm.tbl_mcht_inf a,apsmgm.t_operators  b,apsmgm.tbl_org_main c,apsmgm.tbl_area   d    " );
			sb.append(" WHERE a.ext1 = b.id AND b.oneagentid = c.org_code AND c.area_id=d.id ");
			sb.append("  and TO_number(replace(a.appl_date, '-', ''))<= '"+maxDate+"'    ");		
			if(!(id==1||id==40001)){
				sb.append(" and a.group_cd = '2' ");
			}
			sb.append(" GROUP BY c.org_name, d.name ）x");
			sb.append( " LEFT JOIN    " );
			sb.append(" (SELECT c.org_name, COUNT(1) AS countMcht FROM apsmgm.tbl_mcht_inf a, apsmgm.t_operators  b, apsmgm.tbl_org_main c" );
			sb.append("  WHERE a.ext1 = b.id AND b.oneagentid = c.org_code");
			sb.append(" and TO_number(replace(a.appl_date, '-', ''))>= '"+minDate+"'");
			sb.append(" and TO_number(replace(a.appl_date, '-', ''))<= '"+maxDate+"'");
			if(!(id==1||id==40001)){
				sb.append(" and a.group_cd = '2'");
			}
			sb.append("  GROUP BY c.org_name )   z  ON   x.org_name =z.org_name  where 1=1 ");
					
			return sb.toString();
	}

	@Override
	protected String getSearchHQL() {
		// TODO Auto-generated method stub
		StringBuffer  sb= new StringBuffer();
		sb.append("SELECT x.org_name, nvl(x.zhong,0), nvl(z.countMcht,0), x.name ");
		sb.append(" FROM (SELECT c.org_name, d.name , COUNT(1) AS zhong " );
		sb.append(" FROM apsmgm.tbl_mcht_inf a,apsmgm.t_operators  b,apsmgm.tbl_org_main c,apsmgm.tbl_area   d    " );
		sb.append(" WHERE a.ext1 = b.id AND b.oneagentid = c.org_code AND c.area_id=d.id "  );
		sb.append("  and TO_number(replace(a.appl_date, '-', ''))<= '"+maxDate+"'    ");
		if(!(id==1||id==40001)){
			sb.append(" and a.group_cd = '2' ");
		}
		sb.append(" GROUP BY c.org_name, d.name ）x");
		sb.append( " LEFT JOIN    " );
		sb.append(" (SELECT c.org_name, COUNT(1) AS countMcht FROM apsmgm.tbl_mcht_inf a, apsmgm.t_operators  b, apsmgm.tbl_org_main c" );
		sb.append("  WHERE a.ext1 = b.id AND b.oneagentid = c.org_code");
		sb.append(" and TO_number(replace(a.appl_date, '-', ''))>= '"+minDate+"'");
		sb.append(" and TO_number(replace(a.appl_date, '-', ''))<= '"+maxDate+"'");
		if(!(id==1||id==40001)){
			sb.append(" and a.group_cd = '2'");
		}
		sb.append("  GROUP BY c.org_name )   z  ON   x.org_name =z.org_name   where 1=1  ");
		return sb.toString();
		
	}

	@Override
	protected String getOrderPartOfHQL() {
		// TODO Auto-generated method stub
		return " order by  x.org_name  ";
	}

	public String getCompanyName() {
		return companyName;
	}

	public void setCompanyName(String companyName) {
		this.companyName = companyName;
	}

	public String getCommNumber() {
		return commNumber;
	}

	public void setCommNumber(String commNumber) {
		this.commNumber = commNumber;
	}

	public String getSunPieceNumber() {
		return sunPieceNumber;
	}

	public void setSunPieceNumber(String sunPieceNumber) {
		this.sunPieceNumber = sunPieceNumber;
	}

	public String getAreaCode() {
		return areaCode;
	}

	public void setAreaCode(String areaCode) {
		this.areaCode = areaCode;
	}

	public String getMinDate() {
		return minDate;
	}

	public void setMinDate(String minDate) {
		this.minDate = minDate;
	}

	public String getMaxDate() {
		return maxDate;
	}

	public void setMaxDate(String maxDate) {
		this.maxDate = maxDate;
	}


	public long getId() {
		return id;
	}

	public void setId(long id) {
		this.id = id;
	}

	
	
	
	
}
